|
Chris Pollett >Old Classes
> |
CS157b Fall 2003Practice Midterm 2The practice midterm is below. To study for the midterm I would suggest you: (1) Know how to do (by heart) all the practice problems. (2) Go over your notes three times. Second and third time try to see how huch you can remember from the first time. (3) Go over the homework problems. (4) Try to create your own problems similar to the ones I have given and solve them. (5) Skim the relevant sections from the book. (6) If you want to study in groups, at this point you are ready to quiz each other. Here are some facts about the actual midterm: (a) The midterm will be in class . (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) If your cell-phone or beeper goes off you will be excused from the test at that point and graded on what you have done till your excusal. (f) One problem (less typos) on the actual test will be from the practice test. 1. Describe with a figure and words how the unpacked, bitmapped data page format works. 2. In Oracle what is the difference between the following groups of catalog views: ALL, DBA, USER. 3. Suppose we have a hash index on (id, age). Which of the following selection conditions does it match? id=3 OR age = 2 id=3 AND age = 2 id =3 AND age > 2. 4. Give some heuristics a query optimizer might use to improve the performance of evaluating a query. For joins what kind of reordering does a query optimizer typically consider? 5. Suppose a record is 50 bytes long, a page is 4096 bytes long, and the relation R is 100000 pages long. Estimate how many I/Os it would take to use the external merge sort algorithm to sort R. Assume we have enough buffers to hold blocking factor + 1 pages. 6. Consider the relation R(a , b). Suppose we have a hash index on a and a B+-tree index on b. Explain how select * from R where a=4 and b=3; might be evaluated using bitmap techniques. 7. Consider the relation R(a, b, c). Suppose R is a 1000 pages long, and in a given record a,b,c take 4 bytes each to store. Estimate the number of I/Os needed to compute select distinct a from R; using hash-based projection. Let our fudge factor f=1.4. What would be the number of buffer pages needed for this algorithm to work well? 8. Suppose we have 10 buffer pages and we want to compute R join S where R has 2000 pages and S has 750 pages using the Block Nested Loops Join algorithm. Estimate the number of I/Os required. 9. Using the same information as in (8) compute the number of I/Os needed if instead we did Sort-Merge Join. 10 Briefly describe how two phase locking works. (Didn't finish convering for midterm) |